const mysql = require('mysql');
const {
	getMSTime,
} = require('../../utils/dateUtils')
const options = {
	// host: "192.168.1.3",
	host: "192.168.1.2",
	user: "root",
	port: 3306,
	// password: "atmd@dd_2015",
	password: "atmd@dd_2014",
	database: "songlist",
	charset: "utf8_general_ci"
}

const connection = mysql.createConnection(options);

connection.connect();

const _FUNCS = ['ABS', 'ABS', 'BIN', 'CEILING', 'EXP', 'FLOOR', 'GREATEST', 'LEAST', 'LN', 'LOG', 'MOD', 'PI', 'RAND', 'ROUND', 'SIGN', 'SQRT', 'TRUNCATE', 'AVG', 'COUNT', 'MIN', 'MAX', 'SUM', 'GROUP_CONCAT', 'ASCII', 'BIT_LENGTH', 'CONCAT', 'CONCAT_WS', 'INSERT', 'FIND_IN_SET', 'LCASE', 'LOWER', 'LEFT', 'LENGTH', 'LTRIM', 'POSITION', 'QUOTE', 'REPEAT', 'REVERSE', 'RIGHT', 'RTRIM', 'STRCMP', 'TRIM', 'UCASE', 'UPPER', 'CURDATE', 'CURRENT_DATE', 'CURTIME', 'CURRENT_TIME', 'DATE_ADD', 'DATE_FORMAT', 'DATE_SUB', 'DAYOFWEEK', 'DAYOFMONTH', 'DAYOFYEAR', 'DAYNAME', 'FROM_UNIXTIME', 'HOUR', 'MINUTE', 'MONTH', 'MONTHNAME', 'NOW', 'QUARTER', 'WEEK', 'YEAR', 'AES_ENCRYPT', 'AES_DECRYPT', 'DECODE', 'ENCRYPT', 'ENCODE', 'MD5', 'PASSWORD', 'SHA', 'DATABASE', 'BENCHMARK', 'CONNECTION_ID', 'FOUND_ROWS', 'USER', 'SYSTEM_USER', 'VERSION']
const _funcSet = new Set(_FUNCS)

module.exports = {

	selectOne(params, source) {
		return parseOptions(params, 'query', 0, source)
	},

	select(params, source) {
		return parseOptions(params, 'query', -1, source)
	},

	insert(params, source) {
		return parseOptions(params, 'add', -1, source)
	},

	insertBatch(params, source) {
		return parseOptions(params, 'batch', -1, source)
	},

	update(params, source) {
		return parseOptions(params, 'update', -1, source)
	},

	remove(params, source) {
		return parseOptions(params, 'delete', -1, source)
	},

	executeSQL(SQL, source) {
		return dbConnection(SQL, -1, source)
	},
}

function parseOptions(options, type, idx, source) {
	if (!options)
		return Promise.reject('SQL错误！')

	if (typeof options == 'string')
		return dbConnection(options, idx, source)

	let {
		table,
		fields = [],
		updates = [],
		updateFeilds = [],
		updateValues = [],
		values = [],
		where = [],
		batch = [],
		condition = '',
	} = options
	fields = fields.map((val, idx) => {
		return val.replace(/[`'"\\\/]/g, '')
	})
	fields = fields.join(',')
	if (updateFeilds.length > 0 && updateValues.length == updateFeilds.length) {
		updates = updateFeilds.map((field, idx) => {
			let val = updateValues[idx]
			val = parseValue(val, idx)
			return `${field} = ${val}`
		})
	}
	updates = updates.join(',')
	values = values.map(parseValue)
	values = values.join(',')
	batch = batch.join(',')
	where.unshift('1 = 1')
	where = where.join(' AND ')
	let SQL = ''
	if (type == 'query')
		SQL = `SELECT ${fields} FROM ${table} WHERE ${where} ${condition};`
	else if (type == 'add')
		SQL = `INSERT INTO ${table} (${fields}) VALUES (${values});`
	else if (type == 'update')
		SQL = `UPDATE ${table} SET ${updates} WHERE ${where};`;
	else if (type == 'batch')
		SQL = `INSERT INTO ${table} (${fields}) VALUES ${batch};`
	else if (type == 'delete')
		SQL = `DELETE FROM ${table} WHERE ${where};`;
	return dbConnection(SQL, idx, source)
}

function dbConnection(SQL, idx, source = '[DEFAULT]') {
	// 记录SQL日志
	// console.log(`[${getMSTime()}]`, `[${source}]`, `SQL ===>>> ${SQL}`);
	return new Promise((resolve, reject) => {
		connection.query(SQL, (error, results, fields) => {
			if (error) {
				console.log(error)
				return resolve({
					failed: true,
					err: error || 'error',
					sql: SQL,
				});
			}
			let rstr = JSON.stringify(results)
			// return resolve({
			// 	results,
			// 	fields,
			// })
			if (typeof idx == 'number' && idx >= 0) {
				results = results && results[idx] || {}
			}
			return resolve(results);
		});
	})
}

function parseValue(val, idx) {
	if (val == 'NOW()')
		return val
	if (/^[A-Z]+\([\s\S]+\)$/.test(val) && _funcSet.has(val.replace(/\([\s\S]*\)$/, '')))
		return val
	if (typeof val == 'boolean' || typeof val == 'number')
		return val
	if (typeof val == 'string')
		val = val.replace(/\\/g, '\\\\')
	if (typeof val == 'string' && val.indexOf("'") < 0)
		return `'${val}'`
	if (typeof val == 'string' && val.indexOf('"') < 0)
		return `"${val}"`
	return `'${val && val.replace(/'/g, "\\'") || ""}'`
}